package pe.unmsm.db;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.channels.FileChannel;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import pe.unmsm.modelo.Bien;
import pe.unmsm.modelo.Dependencia;
import pe.unmsm.modelo.Dependencias;
import pe.unmsm.modelo.Equipo;
import pe.unmsm.modelo.Equipos;
import pe.unmsm.modelo.Mueble;
import pe.unmsm.modelo.Muebles;
import pe.unmsm.modelo.Usuario;
import pe.unmsm.modelo.Vehiculo;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;

public class InventarioHelper9 extends SQLiteOpenHelper {

	private static String DB_PATH = "/data/data/pe.unmsm.inventario/databases/";
	public static final int DATABASE_VERSION = 1;
	public static final String DB_NAME = "ronaldj19.db";
	private SQLiteDatabase myDataBase;
	private Context mContext;

	public InventarioHelper9(Context context, String nombre,
			CursorFactory factory, int version) {
		super(context, DB_NAME, null, DATABASE_VERSION);
		mContext = context;
	}

	public void createDataBase() throws IOException {

		boolean dbExist = checkDataBase();
		if (!dbExist) {
			this.getReadableDatabase();
			try {
				copyDataBase();
			} catch (IOException e) {
				// Error al copiar la base de datos
				throw new Error("Error copiando database");
			}
		}
	}

	private boolean checkDataBase() {
		SQLiteDatabase checkDB = null;
		try {
			String myPath = DB_PATH + DB_NAME;
//			String myPath = "/opt/android/tools/sd/Exportacion.db";
			checkDB = SQLiteDatabase.openDatabase(myPath, null,
					SQLiteDatabase.OPEN_READONLY);
		} catch (SQLiteException e) {
			// Database doesn't exist
		}
		if (checkDB != null) {
			checkDB.close();
		}
		return checkDB != null ? true : false;
	}

	private void copyDataBase() throws IOException {

		OutputStream databaseOutputStream = new FileOutputStream("" + DB_PATH
				+ DB_NAME);
		InputStream databaseInputStream;

		byte[] buffer = new byte[1024];
		int length;

		databaseInputStream = mContext.getAssets().open("Exportacion.db");

		while ((length = databaseInputStream.read(buffer)) > 0) {
			databaseOutputStream.write(buffer);
		}

		databaseInputStream.close();
		databaseOutputStream.flush();
		databaseOutputStream.close();
		
//		InputStream myInput ;
//
//		try {
//			myInput = new FileInputStream("/sdcard/Exportacion.db");
//			File directory = new File(
//					"/data/data/pe.unmsm.inventario/databases");
//
//			if (!directory.exists()) {
//				directory.mkdirs();
//
//			}else{
//				System.out.println("No existe la carpeta en la SD");
//			}
//
//			OutputStream myOutput = new FileOutputStream("" + DB_PATH
//					+ DB_NAME);
//			byte[] buffer = new byte[1024];
//			int length;
//			while ((length = myInput.read(buffer)) > 0) {
//				myOutput.write(buffer);
//			}
//
//			
//			myInput.close();
//			myOutput.flush();
//			myOutput.close();
//			
//			
//
//		} catch (FileNotFoundException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		
		
	}

	public void openDataBase() throws SQLException {

		// Open the database
		String myPath = DB_PATH + DB_NAME;
//		String myPath = "/opt/android/tools/sd/Exportacion.db";
		
		myDataBase = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READONLY
						+ SQLiteDatabase.NO_LOCALIZED_COLLATORS);
	}

	@Override
	public synchronized void close() {

		if (myDataBase != null)
			myDataBase.close();

		super.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	public List<Dependencias> getAllDependencia() {

		List<Dependencias> d = new ArrayList<Dependencias>();
		// Select All Query
		String selectQuery = "SELECT  * FROM Dependencia";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Dependencias dep = new Dependencias();

				dep.setCodigo(cursor.getString(cursor.getColumnIndex("codigo")));
				dep.setAmbiente1(cursor.getString(cursor
						.getColumnIndex("ambiente1")));
				dep.setCodArea(cursor.getString(cursor
						.getColumnIndex("codArea")));
				dep.setCodInstitucional(cursor.getString(cursor
						.getColumnIndex("codInstitucional")));
				dep.setCodLocal(cursor.getString(cursor
						.getColumnIndex("codLocal")));
				dep.setCodUsuario(cursor.getString(cursor
						.getColumnIndex("codUsuario")));
				dep.setDependencia(cursor.getString(cursor
						.getColumnIndex("dependencia")));
				dep.setLocal(cursor.getString(cursor.getColumnIndex("locall")));
				dep.setNombre(cursor.getString(cursor.getColumnIndex("nombre")));
				dep.setSubUnidad(cursor.getString(cursor
						.getColumnIndex("subUnidad")));
				dep.setTipo(cursor.getString(cursor.getColumnIndex("tipo")));
				dep.setUbicacion(cursor.getString(cursor
						.getColumnIndex("ubicacion")));
				dep.setUnidad(cursor.getString(cursor.getColumnIndex("unidad")));
				d.add(dep);
			} while (cursor.moveToNext());
		}

		// cerrar la conexion
		cursor.close();
		db.close();

		// returning lables
		return d;
	}

	public List<String> getAllDependencia1Padre() {
		List<String> dependenciapadre = new ArrayList<String>();

		// Select All Query
		String selectQuery = "select nombre from Dependencia where tipo = 'Dependencia'";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				dependenciapadre.add(cursor.getString(0));
			} while (cursor.moveToNext());
		}

		// closing connection
		cursor.close();
		db.close();

		// returning lables
		return dependenciapadre;
	}

	public List<String> getPersonalizado(int a, String query) {
		List<String> dependencia = new ArrayList<String>();

		// Select All Query
		String selectQuery = "query";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				dependencia.add(cursor.getString(a));
			} while (cursor.moveToNext());
		}

		// closing connection
		cursor.close();
		db.close();

		// returning lables
		return dependencia;
	}

	public List<Dependencias> cod(String dependencia) {
		List<Dependencias> codigos = new ArrayList<Dependencias>();

		int i = 1;
		// Select All Query
		String selectQuery = "SELECT * FROM Dependencia WHERE substr(codigo,1,2)=='"
				+ dependencia + "'";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {
				if (i == 1) {
					i++;
				} else {

					Dependencias dep = new Dependencias();
					dep.setCodigo(cursor.getString(cursor
							.getColumnIndex("codigo")));
					dep.setAmbiente1(cursor.getString(cursor
							.getColumnIndex("ambiente1")));
					dep.setCodArea(cursor.getString(cursor
							.getColumnIndex("codArea")));
					dep.setCodInstitucional(cursor.getString(cursor
							.getColumnIndex("codInstitucional")));
					dep.setCodLocal(cursor.getString(cursor
							.getColumnIndex("codLocal")));
					dep.setCodUsuario(cursor.getString(cursor
							.getColumnIndex("codUsuario")));
					dep.setDependencia(cursor.getString(cursor
							.getColumnIndex("dependencia")));
					dep.setLocal(cursor.getString(cursor
							.getColumnIndex("locall")));
					dep.setNombre(cursor.getString(cursor
							.getColumnIndex("nombre")));
					dep.setSubUnidad(cursor.getString(cursor
							.getColumnIndex("subUnidad")));
					dep.setTipo(cursor.getString(cursor.getColumnIndex("tipo")));
					dep.setUbicacion(cursor.getString(cursor
							.getColumnIndex("ubicacion")));
					dep.setUnidad(cursor.getString(cursor
							.getColumnIndex("unidad")));
					codigos.add(dep);

				}
			} while (cursor.moveToNext());
		}

		// closing connection
		cursor.close();
		db.close();

		// returning lables
		return codigos;
	}

	public List<Dependencias> cod2(String dependencia) {
		List<Dependencias> codigos = new ArrayList<Dependencias>();
		int i = 1;
		// Select All Query
		String selectQuery = "SELECT * FROM Dependencia WHERE substr(codigo,1,5)=='"
				+ dependencia + "'";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {
				if (i == 1) {
					i++;
				} else {
					Dependencias dep = new Dependencias();
					dep.setCodigo(cursor.getString(cursor
							.getColumnIndex("codigo")));
					dep.setAmbiente1(cursor.getString(cursor
							.getColumnIndex("ambiente1")));
					dep.setCodArea(cursor.getString(cursor
							.getColumnIndex("codArea")));
					dep.setCodInstitucional(cursor.getString(cursor
							.getColumnIndex("codInstitucional")));
					dep.setCodLocal(cursor.getString(cursor
							.getColumnIndex("codLocal")));
					dep.setCodUsuario(cursor.getString(cursor
							.getColumnIndex("codUsuario")));
					dep.setDependencia(cursor.getString(cursor
							.getColumnIndex("dependencia")));
					dep.setLocal(cursor.getString(cursor
							.getColumnIndex("locall")));
					dep.setNombre(cursor.getString(cursor
							.getColumnIndex("nombre")));
					dep.setSubUnidad(cursor.getString(cursor
							.getColumnIndex("subUnidad")));
					dep.setTipo(cursor.getString(cursor.getColumnIndex("tipo")));
					dep.setUbicacion(cursor.getString(cursor
							.getColumnIndex("ubicacion")));
					dep.setUnidad(cursor.getString(cursor
							.getColumnIndex("unidad")));
					codigos.add(0, dep);
				}
			} while (cursor.moveToNext());
		}

		// closing connection
		cursor.close();
		db.close();

		// returning lables
		return codigos;
	}

	public List<Dependencias> cod3(String dependencia) {
		List<Dependencias> codigos = new ArrayList<Dependencias>();
		int i = 1;
		// Select All Query
		String selectQuery = "SELECT * FROM Dependencia WHERE substr(codigo,1,8)=='"
				+ dependencia + "'";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {
				if (i == 1) {
					i++;
				} else {

					Dependencias dep = new Dependencias();
					dep.setCodigo(cursor.getString(cursor
							.getColumnIndex("codigo")));
					dep.setAmbiente1(cursor.getString(cursor
							.getColumnIndex("ambiente1")));
					dep.setCodArea(cursor.getString(cursor
							.getColumnIndex("codArea")));
					dep.setCodInstitucional(cursor.getString(cursor
							.getColumnIndex("codInstitucional")));
					dep.setCodLocal(cursor.getString(cursor
							.getColumnIndex("codLocal")));
					dep.setCodUsuario(cursor.getString(cursor
							.getColumnIndex("codUsuario")));
					dep.setDependencia(cursor.getString(cursor
							.getColumnIndex("dependencia")));
					dep.setLocal(cursor.getString(cursor
							.getColumnIndex("locall")));
					dep.setNombre(cursor.getString(cursor
							.getColumnIndex("nombre")));
					dep.setSubUnidad(cursor.getString(cursor
							.getColumnIndex("subUnidad")));
					dep.setTipo(cursor.getString(cursor.getColumnIndex("tipo")));
					dep.setUbicacion(cursor.getString(cursor
							.getColumnIndex("ubicacion")));
					dep.setUnidad(cursor.getString(cursor
							.getColumnIndex("unidad")));
					codigos.add(0, dep);
				}
			} while (cursor.moveToNext());
		}

		// closing connection
		cursor.close();
		db.close();

		// returning lables
		return codigos;
	}

	public List<Dependencias> cod4(String dependencia) {
		List<Dependencias> codigos = new ArrayList<Dependencias>();
		int i = 1;
		// Select All Query
		String selectQuery = "SELECT * FROM Dependencia WHERE substr(codigo,1,11)=='"
				+ dependencia + "'";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {
				if (i == 1) {
					i++;
				} else {

					Dependencias dep = new Dependencias();
					dep.setCodigo(cursor.getString(cursor
							.getColumnIndex("codigo")));
					dep.setAmbiente1(cursor.getString(cursor
							.getColumnIndex("ambiente1")));
					dep.setCodArea(cursor.getString(cursor
							.getColumnIndex("codArea")));
					dep.setCodInstitucional(cursor.getString(cursor
							.getColumnIndex("codInstitucional")));
					dep.setCodLocal(cursor.getString(cursor
							.getColumnIndex("codLocal")));
					dep.setCodUsuario(cursor.getString(cursor
							.getColumnIndex("codUsuario")));
					dep.setDependencia(cursor.getString(cursor
							.getColumnIndex("dependencia")));
					dep.setLocal(cursor.getString(cursor
							.getColumnIndex("locall")));
					dep.setNombre(cursor.getString(cursor
							.getColumnIndex("nombre")));
					dep.setSubUnidad(cursor.getString(cursor
							.getColumnIndex("subUnidad")));
					dep.setTipo(cursor.getString(cursor.getColumnIndex("tipo")));
					dep.setUbicacion(cursor.getString(cursor
							.getColumnIndex("ubicacion")));
					dep.setUnidad(cursor.getString(cursor
							.getColumnIndex("unidad")));
					codigos.add(0, dep);
				}
			} while (cursor.moveToNext());
		}

		// closing connection
		cursor.close();
		db.close();

		// returning lables
		return codigos;
	}

	public List<Dependencias> cod5(String dependencia) {
		List<Dependencias> codigos = new ArrayList<Dependencias>();
		int i = 1;
		// Select All Query
		String selectQuery = "SELECT * FROM Dependencia WHERE substr(codigo,1,14)=='"
				+ dependencia + "'";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {
				if (i == 1) {
					i++;
				} else {

					Dependencias dep = new Dependencias();
					dep.setCodigo(cursor.getString(cursor
							.getColumnIndex("codigo")));
					dep.setAmbiente1(cursor.getString(cursor
							.getColumnIndex("ambiente1")));
					dep.setCodArea(cursor.getString(cursor
							.getColumnIndex("codArea")));
					dep.setCodInstitucional(cursor.getString(cursor
							.getColumnIndex("codInstitucional")));
					dep.setCodLocal(cursor.getString(cursor
							.getColumnIndex("codLocal")));
					dep.setCodUsuario(cursor.getString(cursor
							.getColumnIndex("codUsuario")));
					dep.setDependencia(cursor.getString(cursor
							.getColumnIndex("dependencia")));
					dep.setLocal(cursor.getString(cursor
							.getColumnIndex("locall")));
					dep.setNombre(cursor.getString(cursor
							.getColumnIndex("nombre")));
					dep.setSubUnidad(cursor.getString(cursor
							.getColumnIndex("subUnidad")));
					dep.setTipo(cursor.getString(cursor.getColumnIndex("tipo")));
					dep.setUbicacion(cursor.getString(cursor
							.getColumnIndex("ubicacion")));
					dep.setUnidad(cursor.getString(cursor
							.getColumnIndex("unidad")));
					codigos.add(0, dep);
				}
			} while (cursor.moveToNext());
		}

		// closing connection
		cursor.close();
		db.close();

		// returning lables
		return codigos;
	}

	// Metodo para devolver los hijos dados el nivel y el ud_pad de la
	// dependencia
	public List<Dependencia> hijos(int nivel, int udpad) {

		System.out.println("InventarioHelper9.hijos()");

		List<Dependencia> hi = new ArrayList<Dependencia>();

		// Select All Query
		String selectQuery = "select * from UNI_DEP u WHERE u.NIVUNICOD ="
				+ nivel + " and u.UD_PAD =" + udpad;
		System.out.print(selectQuery);

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {
				Dependencia dep = new Dependencia();
				dep.setUd_id(cursor.getInt(0));
				dep.setUd_cod(cursor.getString(1));
				dep.setUd_desc(cursor.getString(2));
				dep.setUd_pad(cursor.getInt(3));
				dep.setUd_fec_ini(cursor.getString(4));
				dep.setUd_fec_cad(cursor.getString(5));
				dep.setNivunicod(cursor.getInt(6));
				dep.setUniniv0(cursor.getInt(7));
				dep.setUniniv1(cursor.getInt(8));
				dep.setUniniv2(cursor.getInt(9));
				dep.setUniniv4(cursor.getInt(10));
				dep.setUniniv5(cursor.getInt(11));
				dep.setUniniv6(cursor.getInt(12));
				dep.setUniniv7(cursor.getInt(13));
				dep.setUnidepcaldep(cursor.getInt(14));
				dep.setUniniv3(cursor.getInt(15));
				hi.add(dep);
				System.out.println("ENTRO.2");
			} while (cursor.moveToNext());
		}

		// closing connection
		cursor.close();
		db.close();

		// returning lables
		return hi;
	}

	public List<String> getAllUsuarios() {
		List<String> nombres = new ArrayList<String>();

		// Select All Query
		String selectQuery = "SELECT  * FROM USUARIOS";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				nombres.add(cursor.getString(1));
			} while (cursor.moveToNext());
		}

		// closing connection
		cursor.close();
		db.close();

		// returning lables
		return nombres;
	}

	public List<Usuario> getAllTodo() {

		List<Usuario> u = new ArrayList<Usuario>();
		// Select All Query
		String selectQuery = "SELECT  * FROM USUARIOS";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Usuario usua = new Usuario();
				usua.setId(cursor.getInt(cursor.getColumnIndex("ID")));
				usua.setUsua(cursor.getString(cursor.getColumnIndex("USUA")));
				usua.setCont(cursor.getString(cursor.getColumnIndex("CONT")));
				u.add(usua);

			} while (cursor.moveToNext());
		}

		// closing connection
		cursor.close();
		db.close();

		// returning lables
		return u;
	}

	public String codigoDependencia(String nombre) {

		String codigo = new String();
		// Select All Query
		String selectQuery = "select codigo from Dependencia where nombre = '"
				+ nombre + "'";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {

				codigo = cursor.getString(0);

			} while (cursor.moveToNext());
		}

		// closing connection
		cursor.close();
		db.close();

		// returning lables
		return codigo;
	}

	public List<String> getAllTrabajador(String escojido) {
		List<String> nombres = new ArrayList<String>();
		String nombre = new String();
		String apellido = new String();
		String todo = new String();

		// Select All Query
		String selectQuery = "SELECT t.[nombres] ,t.[apellidos] ,t.[dependencia_codigo] "
				+ " FROM Trabajador t "
				+ " WHERE substr(t.[dependencia_codigo],1,2)=='"
				+ escojido
				+ "'";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				apellido = cursor.getString(1);
				nombre = cursor.getString(0);
				todo = apellido + " " + nombre;
				nombres.add(todo);
			} while (cursor.moveToNext());
		}

		// closing connection
		cursor.close();
		db.close();

		// returning lables
		return nombres;
	}

	public String codigoTrabajador(String apellido, String nombre) {

		String codigo = new String();

		// Select All Query
		String selectQuery = "select codigo from Trabajador where apellidos ='"
				+ apellido + "' and nombres = '" + nombre + "'";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				codigo = cursor.getString(0);
			} while (cursor.moveToNext());
		}

		// closing connection
		cursor.close();
		db.close();

		// returning lables
		return codigo;
	}

	public List<Bien> susEquipos(String codigo_trabajador) {
		List<Bien> bien = new ArrayList<Bien>();
		// Select All Query
		String selectQuery = "select descripcion,codigoSBN,estado from Bien where trabajador_codigo = '"
				+ codigo_trabajador + "'";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Bien v = new Bien();
				v.setCodigoSBN(cursor.getString(cursor
						.getColumnIndex("codigoSBN")));
				v.setDescripcion(cursor.getString(cursor
						.getColumnIndex("descripcion")));
				v.setEstado(cursor.getString(cursor.getColumnIndex("estado")));
				v.setCondicion(null);
				v.setDependencia_codigo(null);
				v.setId(0);
				v.setObservacion(null);
				v.setPrecio(0);
				v.setTipoBien_codigo(null);
				v.setTrabajador_codigo(null);
				bien.add(v);

			} while (cursor.moveToNext());
		}

		// closing connection
		cursor.close();
		db.close();

		// returning lables
		return bien;
	}

	// me retornara el tipo del bien
	public String tipoBien(String sbn) {

		String tipo = new String();

		// Select All Query
		String selectQuery = "select t.[categoria] from Bien b, TipoBien t where b.[codigoSBN] = '"
				+ sbn + "' and t.[codigo]=b.[tipoBien_codigo]";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				tipo = cursor.getString(0);
			} while (cursor.moveToNext());
		}

		// closing connection
		cursor.close();
		db.close();

		// returning lables
		return tipo;
	}

	// me retorna el equipo con sbn
	public Equipos equipo(String sbn) {
		Equipos bien = new Equipos();
		// Select All Query
		String selectQuery = "select b.[codigoSBN],b.[descripcion],b.[estado],b.[precio], e.[marca],e.[modelo],e.[serie]"
				+ "from Bien b,Equipo e "
				+ "where b.[codigoSBN] = '"
				+ sbn
				+ "'and b.[id]=e.[id]";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {

				bien.setCodigoSbn(cursor.getString(cursor
						.getColumnIndex("codigoSBN")));
				bien.setDescripcion(cursor.getString(cursor
						.getColumnIndex("descripcion")));
				bien.setEstado(cursor.getString(cursor.getColumnIndex("estado")));
				bien.setPrecio(cursor.getString(cursor.getColumnIndex("precio")));
				bien.setMarca(cursor.getString(cursor.getColumnIndex("marca")));
				bien.setModelo(cursor.getString(cursor.getColumnIndex("modelo")));
				bien.setSerie(cursor.getString(cursor.getColumnIndex("serie")));

			} while (cursor.moveToNext());
		}

		// closing connection
		cursor.close();
		db.close();

		// returning lables
		return bien;
	}

	// me retorna el mueble con sbn
	public Mueble mueble(String sbn) {
		Mueble mueble = new Mueble();
		// Select All Query
		String selectQuery = "select b.[codigoSBN],b.[descripcion],b.[estado],b.[precio], m.[color],m.[dimension],m.[material]"
				+ "from Bien b,Mueble m "
				+ "where b.[codigoSBN] = '"
				+ sbn
				+ "'and b.[id]=m.[id]";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {

				mueble.setCodigoSbn(cursor.getString(cursor
						.getColumnIndex("codigoSBN")));
				mueble.setDescripcion(cursor.getString(cursor
						.getColumnIndex("descripcion")));
				mueble.setEstado(cursor.getString(cursor
						.getColumnIndex("estado")));
				mueble.setPrecio(cursor.getString(cursor
						.getColumnIndex("precio")));
				mueble.setColor(cursor.getString(cursor.getColumnIndex("color")));
				mueble.setDimension(cursor.getString(cursor
						.getColumnIndex("dimension")));
				mueble.setMaterial(cursor.getString(cursor
						.getColumnIndex("material")));

			} while (cursor.moveToNext());
		}

		// closing connection
		cursor.close();
		db.close();

		// returning lables
		return mueble;
	}

	// backup
//	public void insertarUsuario(int idUsuario, String nombre, String email){
//		 SQLiteDatabase db = getWritableDatabase();
//		 if(db!=null){
//		  db.execSQL("INSERT INTO " + TableUsuarios + 
//		  " (id_usuario, nombre, email) " +
//		  " VALUES(" + idUsuario + ", '" + nombre + "', '" + email +"' ) ");
//		  db.close();   
//		 }
//		}
	
	public void backupdDatabase() {
		try {
			File sd = Environment.getExternalStorageDirectory();
			File data = Environment.getDataDirectory();
			String packageName = "pe.unmsm.inventario";
			String sourceDBName = "ronaldj19.db";
			// String targetDBName = "backup";
			if (sd.canWrite()) {
				Date now = new Date();
				String currentDBPath = "\\data\\" + packageName
						+ "\\databases\\" + sourceDBName;
				SimpleDateFormat dateFormat = new SimpleDateFormat(
						"yyyy-MM-dd-HH-mm");
				// String backupDBPath = targetDBName + dateFormat.format(now) +
				// ".db";
				String backupDBPath = "sourceDBName";
				// data=directorio del sdcar
				File currentDB = new File(data, currentDBPath);
				File backupDB = new File(sd, backupDBPath);

				Log.i("backup", "backupDB=" + backupDB.getAbsolutePath());
				Log.i("backup", "sourceDB=" + currentDB.getAbsolutePath());

				if (currentDB.exists()) {
					FileChannel src = new FileInputStream(currentDB)
							.getChannel();
					FileChannel dst = new FileOutputStream(backupDB)
							.getChannel();
					dst.transferFrom(src, 0, src.size());
					src.close();
					dst.close();
				}
			}
		} catch (Exception e) {
			Log.i("Backup", e.toString());
		}
	}

	// //traer backup del SDCARD al la memoria interna

	public void backup2() throws IOException {
		InputStream myInput = null;

		try {
			myInput = new FileInputStream("/sdcard/Exportacion.db");
			File directory = new File(
					"/data/data/pe.unmsm.inventario/databases");

			if (!directory.exists()) {
				directory.mkdirs();

			}

			OutputStream myOutput = new FileOutputStream(directory.getPath()
					+ "/ronaldj1111111.db");
			byte[] buffer = new byte[1024];
			int length;
			while ((length = myInput.read(buffer)) > 0) {
				myOutput.write(buffer, 0, length);
			}

			myOutput.flush();

			myOutput.close();

			myInput.close();

		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	
}
